package com.sq.partner.manager.util;

import java.math.BigDecimal;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.Date;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellValue;
import org.apache.poi.ss.usermodel.FormulaEvaluator;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFCell;

import com.sq.partner.manager.common.exception.AppException;

public class ExcelUtil {
	public static void insertRow(Workbook wb, Sheet sheet, int startRow,int rows) {
		 
		  sheet.shiftRows(startRow + 1, sheet.getLastRowNum(), rows,true,false);
		  //  Parameters:
		  //   startRow - the row to start shifting
		  //   endRow - the row to end shifting
		  //   n - the number of rows to shift
		  //   copyRowHeight - whether to copy the row height during the shift
		  //   resetOriginalRowHeight - whether to set the original row's height to the default

		  for (int i = 0; i < rows; i++) {
			  //copyRow(sheet, startRow, ++startRow);
		  }
		  
	}
	
	 /**
	  * 获取数字，不是数字也不是字符型数字，返回0
	  * @param cell
	  * @return
	  */
	 public static double getCellNumber(Cell cell){
		 double val = 0.0;
		 if(cell != null){
			 FormulaEvaluator evaluator = cell.getSheet().getWorkbook().getCreationHelper().createFormulaEvaluator();
			 CellValue value = evaluator.evaluate(cell);
			 if(value == null){
				 return val;
			 }
			 int cType = value.getCellType();
			 switch (cType) {
			    case Cell.CELL_TYPE_NUMERIC:
			    	val = value.getNumberValue();
			     break;
			    case Cell.CELL_TYPE_STRING://有时空白单元格会是string型
			    	String varStr = value.getStringValue();
			    	if(varStr != null && !"".equals(varStr)){
			    		val = Double.parseDouble(varStr);
			    	}
			     break;
			 }
		 }
		 return val;
	 }
	 /**
	  * 获取日期
	  * @param cell
	  * @return
	 * @throws ParseException 
	  */
	 public static Date getCellDate(HSSFCell trxDateCell) throws AppException, ParseException {
		 
		 
		 SimpleDateFormat sdf=new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
		 SimpleDateFormat sdf1 = new SimpleDateFormat("yyyy-MM-dd");
		 Date date = null;
         if (trxDateCell.getCellType() == XSSFCell.CELL_TYPE_NUMERIC) {
        	 return trxDateCell.getDateCellValue();
         } else if(trxDateCell.getCellType() == XSSFCell.CELL_TYPE_STRING){
        	 String result = trxDateCell.getStringCellValue();
        	 try{
        	    date = sdf.parse(result);
        	}catch(ParseException p){
        		try{
        			date = sdf1.parse(result);
        		}catch(ParseException p1){
        			throw new AppException("日期格式不正确，正确的格式为：yyyy-MM-dd 或  yyyy-MM-dd HH:mm:ss");
        		}
        	}
        	 return  date;
         }else{
        	 throw new AppException("类型不是日期格式！");
         }
         
     }
	 /**
	  * 获取字符 数字转成字符数字，空的返回空串
	  * @param cell
	  * @return
	  */
	 public static String getCellText(Cell cell){
		 String val = "";
		 if(cell != null){
			 FormulaEvaluator evaluator = cell.getSheet().getWorkbook().getCreationHelper().createFormulaEvaluator();
			 CellValue value = evaluator.evaluate(cell);
			 if(value == null){
				 return val;
			 }
			 int cType = value.getCellType();
			 switch (cType) {
			    case Cell.CELL_TYPE_NUMERIC:
			    	val = String.valueOf(new BigDecimal(value.getNumberValue()));
			     break;
			    case Cell.CELL_TYPE_STRING://有时空白单元格会是string型
			    	 val = value.getStringValue();
			     break;
			     case Cell.CELL_TYPE_BOOLEAN:
			    	 val = String.valueOf(value.getBooleanValue());
			     break;
			     case Cell.CELL_TYPE_ERROR:
			    	 val = String.valueOf(value.getErrorValue());
			 }
		 }
		 return val;
	 }
	 
	 /**
	  * 将sourceCell的值赋给指定cell,如果没有则创建一个cell,如果sourceCell为空，则不赋值
	  * @param row
	  * @param cellIdx
	  * @param sourceCell
	  * @return
	  */
	 public static Cell setCellValue(Row row,int cellIdx,Cell sourceCell){
		 Cell cell = row.getCell(cellIdx);
		 if(cell == null){
			 cell = row.createCell(cellIdx);
		 }
		 
		 if(sourceCell != null){
			 FormulaEvaluator evaluator = sourceCell.getSheet().getWorkbook().getCreationHelper().createFormulaEvaluator();
			 CellValue value = evaluator.evaluate(sourceCell);
			 if(value == null){
				 cell.setCellValue("");
				 return cell;
			 }
			 switch (value.getCellType()){
				 case Cell.CELL_TYPE_NUMERIC:
					 cell.setCellValue(value.getNumberValue());
					 break;
				 case Cell.CELL_TYPE_STRING:
					 cell.setCellValue(value.getStringValue());
					 break;
				 case Cell.CELL_TYPE_BOOLEAN:
					 cell.setCellValue(value.getBooleanValue());
					 break;
				 case Cell.CELL_TYPE_ERROR:
					 cell.setCellValue(value.getErrorValue());
					 break;
			 }
		 }
		 return cell;
	 }
	 
	 /**
	  * 将sourceCell的值转成数字型赋给指定cell,如果没有则创建一个cell,如果sourceCell为空，则不赋值
	  * @param row
	  * @param cellIdx
	  * @param sourceCell
	  * @return
	  */
	 public static Cell setCellNum(Row row,int cellIdx,Cell sourceCell){
		 Cell cell = row.getCell(cellIdx);
		 if(cell == null){
			 cell = row.createCell(cellIdx);
		 }
		 
		 if(sourceCell != null){
			 cell.setCellValue(getCellNumber(sourceCell));
			
		 }
		 return cell;
	 }
}
